#!bin/bash
###编写人：杨政壹
###编写日期：2024/07/11
###版本功能：编写hql语句进行数据的清洗
echo "---------------------开始清洗合同信息表的数据--------------------"
hive -v -e "set hive.exec.mode.local.auto=true; \
insert overwrite table xm02dw.dw_cont_info
select
cont_id,
cust_type,
cust_id,
cust_name,
cont_type,
cont_status,
exec_date,
effe_date,
expiry_date,
cont_amount,
curr_type,
int_rate,
penal_Rate,
repa_fq,
inme_amount,
repa_date,
repa_number,
repa_method,
guar_info,
colla_dp,
loan_purpose,
over_status,
overd_days,
overd_amount,
overd_fees,
overd_intan,
risk_class,
spec_reserve,
inst_id,
am_id,
ie_date,
iu_date,
ta_credit,
tacc_id,
from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as etl_time
from xm02ods.ods_cont_info;"
echo $?
c1=`hive -e "set hive.exec.mode.local.auto=true;select count(1) from xm02ods.ods_cont_info;"`
echo"-----------来源表的数据量为${c1}条-----------"
c2=`hive -e "set hive.exec.mode.local.auto=true;select count(1) from xm02dw.dw_cont_info;"`
echo"-----------落地表的数据量为${c2}条-----------"
if [ ${c1} -eq ${c2} ];then
echo "清洗成功"
else
echo "清洗失败，请检查脚本"


#!bin/bash
###编写人：杨政壹
###编写日期：2024/07/12
###版本功能：编写hql语句进行数据的清洗
echo "---------------------开始清洗贷款申请表的数据--------------------"
hive -v -e "set hive.exec.mode.local.auto=true; \
insert overwrite table xm02dw.dw_loan_apfo
select
app_id,
cust_type,
cust_id,
cust_name,
nc_id,
cont_info,
address,
regexp_replace(eb_info,'/','') as eb_info,
i_revenue,
l_amount,
regexp_replace(indu_class,'/','') as indu_class,
s_repay,
regexp_replace(asset_info,'/','') as asset_info,
regexp_replace(guara_info,'/','') as guara_info,
regexp_replace(colla_dp,'/','') as colla_dp,
repay_plan,
db_account,
loan_purpose,
e_years,
ec_name,
ec_id,
ec_phone,
app_date,
cr_number,
pl_records,
email,
inst_id,
am_id,
a_status,
a_date,
from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as etl_time
from xm02ods.ods_loan_apfo;"
echo $?
c1=`hive -e "set hive.exec.mode.local.auto=true;select count(1) from xm02ods.ods_loan_apfo;"`
echo"-----------来源表的数据量为${c1}条-----------"
c2=`hive -e "set hive.exec.mode.local.auto=true;select count(1) from xm02dw.dw_loan_apfo;"`
echo"-----------落地表的数据量为${c2}条-----------"
if [ ${c1} -eq ${c2} ];then
echo "清洗成功"
else
echo "清洗失败，请检查脚本"